package com.cf.studentsociety.dao.impl;

import com.cf.studentsociety.dao.BaseDao;
import com.cf.studentsociety.dao.MemberDao;

import com.cf.studentsociety.entity.Member;
import com.sun.org.apache.xalan.internal.xsltc.dom.SimpleResultTreeImpl;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
public class MemberDaoImpl extends BaseDao implements MemberDao {
    private Object id;

    @Override
    public Integer addMember(Member member) throws SQLException {
        String sql = "insert into member (memberStudentId,joinDate,member_societyId) value (?,?,?)";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.update(sql,member.getMemberStudentId(),member.getJoinDate(),member.getMember_societyId());
    }
    @Override
    public Integer isMemberIn(Member member) throws SQLException {
        String sql = "select count(1) from member where member_societyId = ? and memberStudentId = ?";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.query(sql, new ScalarHandler<Long>(), member.getMember_societyId(), member.getMemberStudentId()).intValue();
    }
    @Override
    public List<Member> queryAllMember(Integer status,Integer societyId) throws SQLException {
        String sql = "select * from member where memberStatus = ? and member_societyId = ?";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.query(sql,new BeanListHandler<Member>(Member.class),status,societyId);
    }
    @Override
    public Integer deleteGraduated(Integer assId) throws SQLException {
        String sql = "update member,student set member.memberStatus = 2 where member.member_societyId = ? and member.memberStudentId = student.studentId and student.graduated = 2";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.update(sql, assId);
    }

    @Override
    public Integer deleteMember(Integer id ,Integer status) throws SQLException {
        String sql = "update member set member.memberStatus = ? where memberId = ?";
//        String sql = "DELETE FROM member WHERE memberId = ?";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.update(sql,status,id);
    }

    @Override
    public Member queryByNumber(String number) throws SQLException {
        // 正确的SQL语句，使用INNER JOIN来连接两个表，并且使用正确的字段引用方式
        String sql = "SELECT m.* FROM member m INNER JOIN student s ON m.memberStudentId = s.studentId WHERE s.studentNumber = ?";

        // 使用QueryRunner对象执行查询
        QueryRunner qr = new QueryRunner(getDataSource());

        // 返回查询结果，BeanHandler将结果集转换为Member对象
        return qr.query(sql, new BeanHandler<Member>(Member.class), number);
    }

    @Override
    public Integer addMembers(Integer societyCreator, Integer societyId, Date joinDate) {
        // SQL insert query
        String sql = "INSERT IGNORE INTO member (member_societyId, memberStudentId, joinDate, memberStatus, memberPosition)\n" +
                "SELECT ?, s.studentId, ?, 1, 1\n" +
                "FROM student s\n" +
                "WHERE s.studentId = ? AND s.graduated = 1;\n";

        // QueryRunner instance
        QueryRunner qr = new QueryRunner();

        // Connection object
        Connection conn = null;
        int rowsAffected = 0;

        try {
            // Get the connection (ensure this is correctly configured)
            conn = getDataSource().getConnection(); // Replace with actual data source method

            // Log SQL and parameters for debugging
            System.out.println("Executing query: " + sql);
            System.out.println("Parameters: societyId = " + societyId + ", joinDate = " + joinDate + ", societyCreator = " + societyCreator);

            // 先检查 member 表中是否已经有该学生的记录
            String checkSql = "SELECT COUNT(*) FROM member WHERE memberStudentId = ? AND member_societyId = ?";
            long count = qr.query(conn, checkSql, new ScalarHandler<Long>(), societyCreator, societyId);

            if (count > 0) {
                System.out.println("该学生已是该社团的成员，跳过插入操作");
            } else {
                // Execute the insert operation
                rowsAffected = qr.update(conn, sql, societyId, joinDate, societyCreator);
                System.out.println("学生成功加入社团");
            }

            // Log the result
            System.out.println("Rows affected: " + rowsAffected);

        } catch (SQLException e) {
            e.printStackTrace();  // Handle the exception (log it or throw a custom exception)
        } finally {
            // Always close the connection in a finally block to prevent leaks
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        // Return the number of rows affected by the insert
        return rowsAffected;
    }

    @Override
    public Integer deleteMembers(Integer id) throws SQLException {
        String sql = "DELETE FROM member WHERE memberId = ?";
        QueryRunner qr = new QueryRunner(getDataSource());
        return qr.update(sql,id);
    }


}